﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using EntityObjects;

namespace DAL_V1.App_Code.DBBroker
{
    public class EmployeeDBBroker
    {
        private SSISDataSet ds;
        private SSISDataSetTableAdapters.EmployeeTableAdapter tda;
        private SSISDataSetTableAdapters.RoleTableAdapter roletda;
        private int index;
        private List<Employee> empe_list;
        private List<Role> role_list;
        private Employee empe;
        private Role role;
        private SSISDataSet.EmployeeDataTable empe_td;
        private SSISDataSet.RoleDataTable role_td;

        //singleton
        private static bool instance_flag = false;
        private static EmployeeDBBroker instance;

        private EmployeeDBBroker()
        {
            ds = new SSISDataSet();
            tda = new SSISDataSetTableAdapters.EmployeeTableAdapter();
            roletda = new SSISDataSetTableAdapters.RoleTableAdapter();
            //tda.Fill(ds.Employee);
            //roletda.Fill(ds.Role);
        }

        public static EmployeeDBBroker getEmployeeDBBroker()
        {
            if (!instance_flag)
            {
                instance = new EmployeeDBBroker();
                instance_flag = true;

                return instance;
            }
            else
            {
                return instance;
            }
        }

        public List<Employee> selectAllEmployee()
        {
            tda.Fill(ds.Employee);
            empe_list = new List<Employee>();
            for (int i = 0; i < ds.Employee.Rows.Count; i++)
            {
                empe = new Employee();
                empe.EmpId = ds.Employee[i].EmpId;
                empe.EmpName = ds.Employee[i].EmpName;
                empe.DeptId = ds.Employee[i].DeptId;
                empe.Email = ds.Employee[i].Email;
                empe.RoleId = ds.Employee[i].RoleId;
                empe.Password = ds.Employee[i].Password;
                empe_list.Add(empe);
            }
            return empe_list;
        }

        public int addEmployee(Employee emp)
        {
            SSISDataSet.EmployeeRow newRow = ds.Employee.NewEmployeeRow();
            newRow.EmpId = emp.EmpId;
            newRow.EmpName = emp.EmpName;
            newRow.DeptId = emp.DeptId;
            newRow.Email = emp.Email;
            newRow.RoleId = Convert.ToInt32(emp.RoleId);
            newRow.Password = emp.Password;
                     
            ds.Employee.Rows.Add(newRow);
            return tda.Update(ds.Employee);
        }

        //private int getIndex(String Id)
        //{
        //    SSISDataSet.EmployeeRow row = ds.Employee.FindByEmpId(Id);
        //    return ds.Employee.Rows.IndexOf(row);
        //}

        private SSISDataSet.EmployeeRow getRowById(String Id)
        {
            tda.Fill(ds.Employee);
            return ds.Employee.FindByEmpId(Id);
        }

        public int updateEmployee(Employee emp, String Id)
        {
            SSISDataSet.EmployeeRow row = getRowById(Id);
            if (row != null)
            {
                if(emp.EmpName != null && !(emp.EmpName.Equals("")))
                {
                    row.EmpName = emp.EmpName;
                }
                if(emp.DeptId != null && !(emp.DeptId.Equals("")))
                {
                row.DeptId = emp.DeptId;
                }
                if(emp.Email != null && !(emp.Email.Equals("")))
                {
                    row.Email = emp.Email;
                }
                if(emp.RoleId != null)
                {
                    row.RoleId = emp.RoleId;
                }
                if(emp.Password != null && !(emp.Password.Equals("")))
                {
                    row.Password = emp.Password;
                }
            }
        
            return tda.Update(ds.Employee);
        }

        public int deleteEmployee(String Id)
        {
            SSISDataSet.EmployeeRow row = getRowById(Id);
            if (row != null)
            {
                ds.Employee.Rows.Remove(row);
            }
            return tda.Update(ds.Employee);
        }

        public Employee selectEmployee(String Id)
        {
            tda.Fill(ds.Employee);
            SSISDataSet.EmployeeRow r = getRowById(Id);
            if (r != null)
            {
                empe = new Employee();
                empe.EmpId = r.EmpId;
                empe.EmpName = r.EmpName;
                empe.DeptId = r.DeptId;
                empe.Email = r.Email;
                empe.RoleId = r.RoleId;
                empe.Password = r.Password;

                return empe;
            }
            else
            {
                return null;
            }
        }

        //Search by Department
        public List<Employee> selectEmployeeByDept(String id)
        {
            tda.Fill(ds.Employee);
            empe_td = tda.GetDataByDeptId(id);
            if (empe_td != null)
            {
                empe_list = new List<Employee>();
                for (int i = 0; i < empe_td.Rows.Count; i++)
                {
                    empe = new Employee();
                    empe.EmpId = empe_td[i].EmpId;
                    empe.EmpName = empe_td[i].EmpName;
                    empe.DeptId = empe_td[i].DeptId;
                    empe.Email = empe_td[i].Email;
                    empe.RoleId = empe_td[i].RoleId;
                    empe.Password = empe_td[i].Password;
                    empe_list.Add(empe);
                }

                return empe_list;
            }
            else
            {
                return null;
            }
        }

        //Search by Role
        public List<Employee> selectEmployeebyRole(int id)
        {
            tda.Fill(ds.Employee);
            empe_td = tda.GetDataByRoleId(id);
            if (empe_td != null)
            {
                empe_list = new List<Employee>();
                for (int i = 0; i < empe_td.Rows.Count; i++)
                {
                    empe = new Employee();
                    empe.EmpId = empe_td[i].EmpId;
                    empe.EmpName = empe_td[i].EmpName;
                    empe.DeptId = empe_td[i].DeptId;
                    empe.Email = empe_td[i].Email;
                    empe.RoleId = empe_td[i].RoleId;
                    empe.Password = empe_td[i].Password;
                    empe_list.Add(empe);
                }

                return empe_list;
            }
            else
            {
                return null;
            }
        }

        ///TNS/////////////////////////////

        public List<Employee> getEmployeebyDeptIdAndEmpName(String deptId, String empName)
        {
            tda.Fill(ds.Employee);
            empe_td = tda.GetDataByDeptIdAndEmpName(deptId, '%' + empName + '%');
            if (empe_td != null)
            {
                empe_list = new List<Employee>();
                for (int i = 0; i < empe_td.Rows.Count; i++)
                {
                    empe = new Employee();
                    empe.EmpId = empe_td[i].EmpId;
                    empe.EmpName = empe_td[i].EmpName;
                    empe_list.Add(empe);
                }

                return empe_list;
            }
            else
            {
                return null;
            }
        }

        public Employee getEmployeebyDeptIdAndRole(String deptId, int roleId)
        {
            tda.Fill(ds.Employee);
            empe_td = tda.GetDataByDeptIdAndRoleId(deptId, roleId);

            if (empe_td.Rows.Count > 0)
            {
                SSISDataSet.EmployeeRow row = (SSISDataSet.EmployeeRow)empe_td.Rows[0];
                Employee emp = new Employee();
                emp.EmpId = row.EmpId;
                emp.EmpName = row.EmpName;
                emp.DeptId = row.DeptId;
                emp.Email = row.Email;
                emp.RoleId = row.RoleId;
                emp.Password = row.Password;

                return emp;
            }

            else
            {
                return null;
            }
        }


        public List<Employee> getEmployeeByListByManager(String deptId, String empId)
        {
            tda.Fill(ds.Employee);
            empe_td = tda.GetDataByDeptIdAndEmpId(deptId, empId);
            if (empe_td.Rows.Count > 0)
            {
                empe_list = new List<Employee>();
                foreach (SSISDataSet.EmployeeRow row in empe_td.Rows)
                {
                    empe = new Employee();
                    empe.EmpId = row.EmpId;
                    empe.EmpName = row.EmpName;
                    empe.DeptId = row.DeptId;
                    empe.Email = row.Email;
                    empe.RoleId = row.RoleId;
                    empe.Password = row.Password;

                    empe_list.Add(empe);
                }

                return empe_list;
            }
            else
            {
                return null;
            }
        }

        public List<Employee> getEmployeeListByNotIncludeEmpIdAndRoleId(String deptId, String empId, int roleId)
        {
            tda.Fill(ds.Employee);
            empe_td = tda.GetDataByNotEmpIdAndRole(deptId, empId, roleId);
            if (empe_td.Rows.Count > 0)
            {
                empe_list = new List<Employee>();
                foreach (SSISDataSet.EmployeeRow row in empe_td.Rows)
                {
                    empe = new Employee();
                    empe.EmpId = row.EmpId;
                    empe.EmpName = row.EmpName;
                    empe.DeptId = row.DeptId;
                    empe.Email = row.Email;
                    empe.RoleId = row.RoleId;
                    empe.Password = row.Password;

                    empe_list.Add(empe);
                }

                return empe_list;
            }
            else
            {
                return null;
            }
        }

        ///////////////////////////////TNS//////////////////////////////////////

        ////////////////////////Savithri///////////////////////////////////////

        public List<Employee> getRequestedEmployee(String itemId, String deptId)
        {
            SSISDataSetTableAdapters.RequestedEmployeeTableAdapter tda = new SSISDataSetTableAdapters.RequestedEmployeeTableAdapter();
            int j = tda.Fill(ds.RequestedEmployee,itemId,deptId);
            if (j > 0)
            {
                empe_list = new List<Employee>();
                foreach (SSISDataSet.RequestedEmployeeRow row in ds.RequestedEmployee.Rows)
                {
                    Employee emp = new Employee();
                    emp.EmpId = row.EmpId;
                    emp.EmpName = row.EmpName;
                    emp.DeptId = row.DeptId;
                    emp.Email = row.Email;
                    emp.RoleId = row.RoleId;
                    emp.Password = row.Password;
                    emp.ReqFormId = row.ReqFormId;
                    empe_list.Add(emp);
                }
                return empe_list;
            }
            else
            {
                return null;
            }
        }

        //////////////////////////Savithri//////////////////////////////////////


        //////////////////////////////////////////////////////////////////////////////

        //Role
        public List<Role> selectAllRole()
        {
            roletda.Fill(ds.Role);
            role_list = new List<Role>();
            for (int i = 0; i < ds.Role.Rows.Count; i++)
            {
                role = new Role();
                role.RoleId = ds.Role[i].RoleId;
                role.RoleName = ds.Role[i].Role;

                //Employee
                empe_list = selectEmployeebyRole(ds.Role[i].RoleId);
                role.Empe_list = empe_list;

                role_list.Add(role);
            }
            return role_list;
        }

        public int addRole(Role role)
        {

            SSISDataSet.RoleRow newRow = ds.Role.NewRoleRow();
            newRow.RoleId = role.RoleId;
            newRow.Role = role.RoleName;

            ds.Role.Rows.Add(newRow);
            return roletda.Update(ds.Role);
        }

        //private int getIndexForRole(int Id)
        //{
        //    SSISDataSet.RoleRow row = ds.Role.FindByRoleId(Id);
        //    return ds.Role.Rows.IndexOf(row);
        //}

        private SSISDataSet.RoleRow getRowById(int Id)
        {
            roletda.Fill(ds.Role);
            return ds.Role.FindByRoleId(Id);
        }

        public int updateRole(Role role, int Id)
        {
            SSISDataSet.RoleRow row = getRowById(Id);
            if (row != null)
            {
                row.Role = role.RoleName;
            }
            
            return roletda.Update(ds.Role);
        }

        public int deleteRole(int Id)
        {
            SSISDataSet.RoleRow row = getRowById(Id);
            if (row != null)
            {
                ds.Role.Rows.Remove(row);
            }
            return roletda.Update(ds.Role);
        }

        public Role selectRole(int Id)
        {
            roletda.Fill(ds.Role);
            SSISDataSet.RoleRow r = getRowById(Id);
            if (r != null)
            {
                role = new Role();
                role.RoleId = r.RoleId;
                role.RoleName = r.Role;

                //Employee
                empe_list = selectEmployeebyRole(r.RoleId);
                role.Empe_list = empe_list;

                return role;
            }
            else
            {
                return null;
            }
        }
    }
}
